﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data.SqlClient;
using System.Collections;
using System.Data;

namespace StockSBDetails
{
    public class SQLServerAgent : StockSBDetails.ISQLAgent
    {
        protected static SQLServerAgent sqlAgent;
        SqlConnection  sqlConnection = null;


        public static SQLServerAgent getInstance()
        {
            if (sqlAgent == null)
                sqlAgent = new SQLServerAgent();
            return sqlAgent;
        }

        public SQLServerAgent()
        {
            String connString = @"Data Source=127.0.0.1;Initial Catalog=MyStock;User ID=sa;Password=mn141319";
            sqlConnection = new SqlConnection(connString);
            sqlConnection.Open();
        }

        protected DbCommand getCommand_internal()
        {
            SqlCommand cmd= new SqlCommand();
            cmd.Connection = getInstance().sqlConnection;
            return cmd;
        }

        public DbTransaction BeginTransation()
        {
            return getInstance().sqlConnection.BeginTransaction();
        }
        public DbParameter createParam(String name, DbType type, Object value)
        {
            SqlParameter p = new SqlParameter(name, type);
            p.Value = value;
            return p;
        }
        public DbParameter createParam(String name,Object value)
        {
            SqlParameter p = new SqlParameter(name,value);
            return p;
        }
        public void executeInsert(String sql, List<String> names, ArrayList ps)
        {
            DbCommand cmd = getCommand();
            cmd.CommandText = sql;
            if(names.Count!=ps.Count)
            {
                throw new Exception("Error in Execure insert , names size != parameters size");
            }
            int sz=names.Count;
            for(int i=0;i<sz;i++)
            {
                   cmd.Parameters.Add(createParam((String)names[i],ps[i]));
            }
                        
            cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            cmd.Dispose();
        }
        public DbCommand getCommand()
        {
            return getInstance().getCommand_internal();
        }
        public DbCommand getCommand(string cmd)
        {
            DbCommand r = getInstance().getCommand_internal();
            if (r != null)
                r.CommandText = cmd;
            return r;

        }
        public DbDataAdapter getAdapter(DbCommand cmd)
        {
            SqlDataAdapter  adap = new  SqlDataAdapter((SqlCommand)cmd);
            return adap;
        }
    }
}
